
package model;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

import org.mybeans.dao.DAOException;
import org.mybeans.factory.MatchArg;
import org.mybeans.factory.RollbackException;

import databeans.FundHistory;
import databeans.Transaction;


public class FundHistoryDAOSQL {
    
    private List<Connection> connectionPool = new ArrayList<Connection>();

    private String jdbcDriver;
    private String jdbcURL;
    private String jdbcUser;
    private String jdbcPass;
    public FundHistoryDAOSQL(String jdbcDriver, String jdbcURL, String jdbcUser, String jdbcPass) {
        this.jdbcDriver = jdbcDriver;
        this.jdbcURL    = jdbcURL;
        this.jdbcUser    = jdbcUser;
        this.jdbcPass    = jdbcPass;
        
        // create table if it does not exist
        try {
            createFundHistoryTable();
        } catch (DAOException e) {
            System.out.println(e.getMessage());
            return;
        }
    
    }
    
    private synchronized Connection getConnection() throws DAOException {
        if (connectionPool.size() > 0) {
            return connectionPool.remove(connectionPool.size()-1);
        }
        
        try {
            Class.forName(jdbcDriver);
        } catch (ClassNotFoundException e) {
            throw new DAOException(e);
        }

        try {
            return DriverManager.getConnection(jdbcURL,jdbcUser,jdbcPass);
        } catch (SQLException e) {
            throw new DAOException(e);
        }
    }
    
    private synchronized void releaseConnection(Connection con) {
        connectionPool.add(con);
    }

    public synchronized FundHistory[] getFundHistories() throws DAOException {
        
        Connection con = null;
        
        try {
            con = getConnection();

            PreparedStatement pstmt = con.prepareStatement("SELECT * FROM  `cfa_fundhistory`");
            //pstmt.setString(1, email);
            ResultSet rs = pstmt.executeQuery();
            
            ArrayList<FundHistory> array = new ArrayList<FundHistory>();

            while (rs.next()) {
                FundHistory tempFundHistory = new FundHistory();
                tempFundHistory.setFundID(rs.getInt("fundID"));
                Date gcal = new Date();
                gcal = rs.getDate("priceDate");
                //gcal.setTime(rs.getDate("priceDate"));
                tempFundHistory.setPriceDate(gcal);
                tempFundHistory.setPrice(rs.getLong("price"));
                array.add(tempFundHistory);
            }
            
            rs.close();
            pstmt.close();
            releaseConnection(con);
            
            FundHistory[] justArray = (FundHistory[]) array.toArray();
            return justArray;
            
        } catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DAOException(e);
        }
    }
    
    public synchronized FundHistory create(FundHistory fundHistory) throws DAOException {
        Connection con = null;
        try {
            
            con = getConnection();
            PreparedStatement pstmt = con.prepareStatement("INSERT INTO cfa_fundhistory (fundID, priceDate, price) VALUES (?,?,?)");
            
            pstmt.setString(1,Integer.toString(fundHistory.getFundID()));
            
            java.sql.Date sqlDate = (java.sql.Date) fundHistory.getPriceDate();
            pstmt.setDate(2, sqlDate);
            
            pstmt.setLong(3, fundHistory.getPrice());

            int count = pstmt.executeUpdate();
            if (count != 1) throw new SQLException("Insert updated "+count+" rows");
            
            pstmt.close();
            releaseConnection(con);
            return fundHistory;
            
        } catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DAOException(e);
        }
    }
    
    public synchronized FundHistory getFundHistory(int fundID, GregorianCalendar pricedate) throws DAOException {
        Connection con = null;
        try {
            FundHistory [] tempArray = getFundHistories(); //not efficient i know
            
            FundHistory tempObject = null;
            for (int i = 0 ; i < tempArray.length; i ++) {
                if (tempArray[i].getFundID() == fundID && tempArray[i].getPriceDate().equals(pricedate)){
                    tempObject = tempArray[i];
                }
            }

            return tempObject;
            
        } catch (Exception e) {
            System.out.println(e.getMessage());
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DAOException(e);
        }
    }
    

    
    public synchronized boolean deleteFundHistory(int fundID, GregorianCalendar pricedate) throws DAOException {
        Connection con = null;
        try {
            
            con = getConnection();
            PreparedStatement pstmt = con.prepareStatement("DELETE FROM cfa_fundhistory WHERE fundID = ? AND pricedate =?");

            pstmt.setInt(1,fundID);
            
            java.sql.Date sqlDate = new java.sql.Date(pricedate.getTimeInMillis());
            pstmt.setDate(2, sqlDate);


            int count = pstmt.executeUpdate();
            
            pstmt.close();
            releaseConnection(con);
            
            if (count != 1) {
                return false;
            }

            return true;
            
        } catch (Exception e) {
            System.out.println(e.getMessage());
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DAOException(e);
        }
    }
    
    public void createFundHistoryTable () throws DAOException {
        Connection con = null;
        try {
            con = getConnection();
            Statement stmt = con.createStatement();
            
            String q = "CREATE TABLE IF NOT EXISTS `cfa_fundhistory` (" +
                      "`fundID` int(11) NOT NULL," + 
                      "`priceDate` datetime NOT NULL," +
                      "`price` long NOT NULL DEFAULT '0'," +
                      "PRIMARY KEY (`fundID`,`priceDate`)" +
                      ") ENGINE=MyISAM DEFAULT CHARSET=latin1;";          
            stmt.executeUpdate(q);         
            stmt.close();
            
            releaseConnection(con);

        } catch (Exception e) {
            System.out.println(e.getMessage());
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DAOException(e);
        }
    }
    
    public FundHistory getLatestPrice(int fundID) throws DAOException {
        Connection con = null;
        try {
            con = getConnection();

            PreparedStatement pstmt = con.prepareStatement("SELECT priceDate,fundID,price FROM cfa_fundhistory WHERE fundID=? ORDER BY priceDate DESC LIMIT 1");
            pstmt.setInt(1,fundID);
            ResultSet rs = pstmt.executeQuery();
            System.out.println(pstmt.toString());
            FundHistory fh;
            if (!rs.next()) {
                fh = null;
            } else {
                fh = new FundHistory(rs.getInt("fundID"),rs.getDate("priceDate"),rs.getLong("price"));
            }
            
            rs.close();
            pstmt.close();
            releaseConnection(con);
            return fh;
            
        } catch (Exception e) {
            System.out.println(e.getMessage());
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DAOException(e);
        }
        /*try {
    
            // Get item at top of list
            FundHistory[] fh = factory.match(MatchArg.max("priceDate"),MatchArg.equals("fundID",fundID));
            FundHistory maxBean = (fh.length==0 ? null : fh[0]);

            return maxBean;
        } catch (RollbackException e) {
            throw new DAOException(e);
        }*/
    }
    
    public String getLatestDateString() throws DAOException {
        Connection con = null;
        try {
            con = getConnection();

            PreparedStatement pstmt = con.prepareStatement("SELECT priceDate FROM cfa_fundhistory ORDER BY priceDate DESC LIMIT 1");
            ResultSet rs = pstmt.executeQuery();
            
            Date d;
            if (!rs.next()) {
                d = null;
            } else {
                d = rs.getDate("priceDate");
            }
            
            rs.close();
            pstmt.close();
            releaseConnection(con);
            
            DateFormat dfm = new SimpleDateFormat("yyyy-MM-dd");

            StringBuilder d1 = new StringBuilder(dfm.format(d));
            
            return d1.toString();
            
        } catch (Exception e) {
            System.out.println(e.getMessage());
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DAOException(e);
        }
        /*try {
    
            // Get item at top of list
            FundHistory[] fh = factory.match(MatchArg.max("priceDate"),MatchArg.equals("fundID",fundID));
            FundHistory maxBean = (fh.length==0 ? null : fh[0]);

            return maxBean;
        } catch (RollbackException e) {
            throw new DAOException(e);
        }*/
    }
    
    public boolean checkDuplicates(int fundID, Calendar cal) throws DAOException {
        boolean gotDuplicates = false;
        Connection con = null;
        try {
            int day = cal.get(Calendar.DATE);
            int month = cal.get(Calendar.MONTH) + 1;
            int year = cal.get(Calendar.YEAR);
            int dow = cal.get(Calendar.DAY_OF_WEEK);
            int dom = cal.get(Calendar.DAY_OF_MONTH);
            int doy = cal.get(Calendar.DAY_OF_YEAR);
            
            DateFormat dfm = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

            Date d1temp = dfm.parse(year+"-"+month+"-"+day+" 00:00:00");
            Date d2temp = dfm.parse(year+"-"+month+"-"+day+" 23:59:59");
            
            StringBuilder d1 = new StringBuilder(dfm.format(d1temp));
            StringBuilder d2 = new StringBuilder(dfm.format(d2temp));
            
            con = getConnection();

            PreparedStatement pstmt = con.prepareStatement("SELECT fundID FROM cfa_fundhistory WHERE fundID=? AND priceDate BETWEEN ? AND ?");
            pstmt.setInt(1,fundID);
            pstmt.setString(2,year+"-"+month+"-"+day+" 00:00:00");
            pstmt.setString(3,year+"-"+month+"-"+day+" 23:59:59");
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                gotDuplicates = true;
            }
            
            rs.close();
            pstmt.close();
            releaseConnection(con);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        return gotDuplicates;
    }
    
    public FundHistory lookup(int fundID) throws DAOException {
        Connection con = null;
        try {
            
            con = getConnection();

            PreparedStatement pstmt = con.prepareStatement("SELECT * FROM cfa_fundhistory WHERE fundID=? order by priceDate DESC LIMIT 1");
            pstmt.setInt(1,fundID);
            ResultSet rs = pstmt.executeQuery();

            FundHistory fh;
            if (!rs.next()) {
                fh = null;
            } else {
                fh = new FundHistory(rs.getInt("fundID"),rs.getDate("priceDate"),rs.getLong("price"));
            }
            
            rs.close();
            pstmt.close();
            releaseConnection(con);
            return fh;
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }
    public FundHistory[] lookupFromtoNow(int fundID,Calendar cal)
    {
    	Connection con = null;
        try {
            int day = cal.get(Calendar.DATE);
            int month = cal.get(Calendar.MONTH)+1;
            int year = cal.get(Calendar.YEAR);
            
            int daynow = Calendar.getInstance().get(Calendar.DATE);
            int monthnow = Calendar.getInstance().get(Calendar.MONTH)+1;
            int yearnow = Calendar.getInstance().get(Calendar.YEAR);
            
            con = getConnection();

            PreparedStatement pstmt = con.prepareStatement("SELECT * FROM cfa_fundhistory WHERE fundID=? AND priceDate BETWEEN ? AND ?");
            pstmt.setInt(1,fundID);
            pstmt.setString(2,year+"-"+month+"-"+day+" 00:00:00");
            pstmt.setString(3,yearnow+"-"+monthnow+"-"+daynow+" 23:59:59");
            System.out.println(pstmt.toString());
            ResultSet rs = pstmt.executeQuery();

            List<FundHistory> list = new ArrayList<FundHistory>();
            
            while(rs.next())
            {
            	FundHistory tempFundHistory = new FundHistory();
                tempFundHistory.setFundID(rs.getInt("fundID"));
                Date gcal = new Date();
                gcal = rs.getDate("priceDate");
                //gcal.setTime(rs.getDate("priceDate"));
                tempFundHistory.setPriceDate(gcal);
                tempFundHistory.setPrice(rs.getLong("price"));
                list.add(tempFundHistory);
            }
            
            rs.close();
            pstmt.close();
            releaseConnection(con);
            return list.toArray(new FundHistory[list.size()]);
            
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    	
    }
    public FundHistory lookup(int fundID, Calendar cal) throws DAOException {
        Connection con = null;
        try {
            int day = cal.get(Calendar.DATE);
            int month = cal.get(Calendar.MONTH) + 1;
            int year = cal.get(Calendar.YEAR);
            int dow = cal.get(Calendar.DAY_OF_WEEK);
            int dom = cal.get(Calendar.DAY_OF_MONTH);
            int doy = cal.get(Calendar.DAY_OF_YEAR);
            
            /*
            DateFormat dfm = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

            Date d1temp = dfm.parse();
            Date d2temp = dfm.parse();
            
            StringBuilder d1 = new StringBuilder(dfm.format(d1temp));
            StringBuilder d2 = new StringBuilder(dfm.format(d2temp));
            */
            con = getConnection();

            PreparedStatement pstmt = con.prepareStatement("SELECT * FROM cfa_fundhistory WHERE fundID=? AND priceDate BETWEEN ? AND ?");
            pstmt.setInt(1,fundID);
            pstmt.setString(2,year+"-"+month+"-"+day+" 00:00:00");
            pstmt.setString(3,year+"-"+month+"-"+day+" 23:59:59");
            ResultSet rs = pstmt.executeQuery();

            FundHistory fh;
            if (!rs.next()) {
                fh = null;
            } else {
                fh = new FundHistory(rs.getInt("fundID"),rs.getDate("priceDate"),rs.getLong("price"));
            }
            
            rs.close();
            pstmt.close();
            releaseConnection(con);
            return fh;
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }
}